package org.firestorm.traficforecast.database;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

public class MySQLConnect {

    private final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    private final String USER;
    private final String PASSWORD;
    private final String URL;
    public static Connection con;
    public static Statement s;

    public static void connectToDb(String driverName, String URL, String userName, String password) {
        try {
            Class.forName(driverName);
            con = DriverManager.getConnection(URL, userName, password);
            if (con != null) {
                s = (Statement) con.createStatement();
                System.out.println("Kết nối thành công tới CSDL!");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(this.URL, this.USER,
                    this.PASSWORD);
        } catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(MySQLConnect.class.getName()).log(Level.SEVERE,
                    null, ex);
        }
        return conn;
    }

    public static void main(String[] args) {
        MySQLConnect connect = new MySQLConnect(
                "localhost:3306/traficforecast", "root", "123456");
        System.out.println(connect.dbIsExist("traficforecast"));
        String stm = "CREATE TABLE daisogiatu (id INTEGER not NULL, ratthap DOUBLE, honthap DOUBLE, khanangthap DOUBLE, itthap DOUBLE, itcao DOUBLE, khanangcao DOUBLE, honcao DOUBLE, ratcao DOUBLE, PRIMARY KEY (id))";
        connect.excuteStatement(stm);
    }

    public MySQLConnect(){
    	Properties props = new Properties();
		try {
			props.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("config.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
		this.PASSWORD = props.getProperty("DB_PASSWORD");
		this.URL = "jdbc:mysql://" + props.getProperty("DB_URL") + "/traficforecast";
		this.USER = props.getProperty("DB_USER");
    }
    
    public MySQLConnect(String url, String user, String pass) {
        this.URL = "jdbc:mysql://" + url;
        this.USER = user;
        this.PASSWORD = pass;
    }

    

    @SuppressWarnings("unused")
    private boolean createDatabase(String dbName) {
        boolean check = false;
        Connection conn = getConnection();
        Statement stmt = null;
        if (conn == null) {
            return check;
        }
        try {
            stmt = conn.createStatement();
            String sql = "CREATE DATABASE " + dbName;
            stmt.executeUpdate(sql);
            check = true;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLConnect.class.getName()).log(Level.SEVERE,
                    null, ex);
        } finally {
            closeConnection(conn, stmt);
        }
        return check;
    }

    public boolean dbIsExist(String dbName) {
        boolean check = false;
        Connection conn = getConnection();
        ResultSet rs;
        try {
            rs = conn.getMetaData().getCatalogs();

            while (rs.next()) {
                if (dbName.equals(rs.getString("TABLE_CAT"))) {
                    return true;
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(MySQLConnect.class.getName()).log(Level.SEVERE,
                    null, ex);
        } finally {
            closeConnection(conn, null);
        }
        return check;
    }

    private boolean excuteStatement(String statement) {
        boolean check = false;
        Connection conn = getConnection();
        Statement stmt = null;
        if (conn == null) {
            return check;
        }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(statement);
            return true;
        } catch (SQLException ex) {
            Logger.getLogger(MySQLConnect.class.getName()).log(Level.SEVERE,
                    null, ex);
        } finally {
            closeConnection(conn, stmt);
        }
        return check;
    }

    public static void closeConnection(Connection conn, Statement stmt) {
        try {
            if (stmt != null) {
                conn.close();
            }
        } catch (SQLException se) {
        }
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException se) {
        }
    }
}
